package com.googlecode.publicholiday.repository.oracle;

import com.googlecode.publicholiday.repository.ImmutablePublicHolidayRepository;
import org.joda.time.LocalDate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.SortedSet;
import java.util.TreeSet;

import static com.googlecode.publicholiday.utils.SqlUtils.close;

public class OracleImmutablePublicHolidayRepository implements ImmutablePublicHolidayRepository {

    private final static Logger LOG = LoggerFactory.getLogger(OracleImmutablePublicHolidayRepository.class);

    private final DataSource dataSource;

    public OracleImmutablePublicHolidayRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public LocalDate find(String countryCode, LocalDate dateInQuestion) {
        LOG.debug("Attempting to find a Public Holiday in: {} on: {}", countryCode, dateInQuestion);
        Connection connection = null;
        CallableStatement statement = null;
        LocalDate localDate = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareCall("{ ? = public_holiday_pkg.find_holidays( ?, ? ) }");
            statement.registerOutParameter(1, Types.BOOLEAN);
            statement.setString(2, countryCode);
            statement.setDate(3, new Date(dateInQuestion.toDate().getTime()));
            statement.executeUpdate();
            if (statement.getDate(1) != null) {
                localDate = new LocalDate(statement.getDate(1).getTime());
                LOG.debug("Found Public Holiday in: {} on: {}", countryCode, dateInQuestion);
            } else {
                LOG.debug("No Public Holiday found in: {} on: {}", countryCode, dateInQuestion);
            }
        } catch (SQLException e) {
            LOG.error("Something went wrong finding a Public Holiday in: " + countryCode + " on: " + dateInQuestion, e);
        } finally {
            close(statement);
            close(connection);
        }
        return localDate;
    }

    @Override
    public Iterable<LocalDate> find(String countryCode, LocalDate startDate, LocalDate endDate) {
        LOG.debug("Attempting to find Public Holidays in: {} between: {} and {}", countryCode, startDate, endDate);
        Connection connection = null;
        CallableStatement statement = null;
        ResultSet resultSet = null;
        SortedSet<LocalDate> publicHolidays = new TreeSet<LocalDate>();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareCall("{ ? = pkg_holiday.find_holidays( ?, ?, ? ) }");
            statement.registerOutParameter(1, Types.ARRAY);
            statement.setString(2, countryCode);
            statement.setDate(3, new Date(startDate.toDate().getTime()));
            statement.setDate(4, new Date(endDate.toDate().getTime()));
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                final LocalDate publicHoliday = new LocalDate(resultSet.getDate(1).getTime());
                publicHolidays.add(publicHoliday);
                LOG.debug("Found Public Holiday in: {} on: {}", countryCode, publicHoliday);
            }
            LOG.debug("Found {} Public Holidays in: {} between: {} and {}", publicHolidays.size(), countryCode, startDate, endDate);
        } catch (SQLException e) {
            LOG.error("Something went wrong finding Public Holidays in: " + countryCode + " between " + startDate + " and " + endDate, e);
        } finally {
            close(connection, statement, resultSet);
        }
        return publicHolidays;
    }

    protected DataSource getDataSource() {
        return dataSource;
    }
}
